drop procedure if exists RSBUGT.LOGIN;
go

create procedure RSBUGT.LOGIN(in login_params long varchar)
begin
    declare error_code integer;
    declare error_msg long varchar;
    declare user_n long varchar;
    declare user_p long varchar;
    declare user_fio long varchar;

    create temporary table if not exists RET_VALUES(VAR_NAME varchar(30), VAR_VALUE varchar(150)) engine = MEMORY;
    truncate table RET_VALUES;

    set error_code = 0;
    set error_msg = '';
    set user_n = GET_PARAM('LOGIN', login_params);
    set user_p = GET_PARAM('PWD', login_params);

    if not exists (select 1 from RSBUGT.USERS where USER_NAME = user_n and USER_PWD = user_p and ACTIVE = 1) then
        set error_code = 101;
        set error_msg = 'Wrong login or password.';
    end if;

    if (error_code = 0) then
        select USER_ID, FIO into @current_user_id, user_fio from RSBUGT.USERS where USER_NAME = user_n and USER_PWD = user_p and ACTIVE = 1;
    end if;
    
    if (error_code = 0) then
        delete from MESSAGES where USER_ID = @current_user_id;
        insert into RET_VALUES(VAR_NAME, VAR_VALUE) values ('USER_FIO', user_fio);
    end if;

    if (error_code = 0) then
        select * from RET_VALUES;
    else
        select error_code as ERROR_CODE, error_msg as ERROR_MSG;
    end if;

    drop table RET_VALUES;
end;
go

grant execute on procedure RSBUGT.LOGIN to 'RSUSR'@'%';
go